﻿using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using GLUpload2.LNPS_DynamicScriptService;
using GLUpload2.QueryService;

namespace GLUpload2
{
    public partial class Lookup : Form
    {
        public string Company
        {
            get;
            set;
        }

        public string CurrentValue
        {
            get;
            set;
        }

        public Excel.Application ExcelParent
        {
            get;
            set;
        }

        public Lookup()
        {
            InitializeComponent();
        }

        public void ChangeTab(string tabName, string value)
        {
            DataGridView dataGridView = dataGridView1;
            for (int i = 0; i < tabControl1.TabPages.Count; i++)
            {
                if (tabControl1.TabPages[i].Name == tabName)
                {
                    tabControl1.SelectedIndex = i;
                    switch (i)
                    {
                        case 0:
                            dataGridView = dataGridView1;
                            break;
                        case 1:
                            dataGridView = dataGridView2;
                            break;
                        case 2:
                            dataGridView = dataGridView3;
                            break;
                        case 3:
                            dataGridView = dataGridView4;
                            break;
                    }
                }
            }
            dataGridView.ClearSelection();
            foreach (DataGridViewRow dataGridViewRow in dataGridView.Rows)
            {
                if (dataGridViewRow.Cells[0].Value.ToString() == value)
                {
                    dataGridViewRow.Selected = true;
                    dataGridView.FirstDisplayedScrollingRowIndex = dataGridViewRow.Index;
                }
            }
        }

        private void Lookup_Load(object sender, EventArgs e)
        {
            try
            {
                // Aif Service Client
                LNPS_DynamicScriptClassClient client = new LNPS_DynamicScriptClassClient();

                // Create an instance of the CallContext class.
                CallContext context = new CallContext();

                // Set context company and language
                context.Company = Company;
                context.Language = "en-gb";

                // Main GL accounts
                string script = "";
                script += "str getMainAccounts()\r\n";
                script += "{\r\n";
                script += "\tMainAccount objMainAccount;\r\n";
                script += "\tstr result = '';\r\n";
                script += "\t;\r\n";
                script += "\tresult += '<ds>';\r\n";
                script += "\twhile SELECT MainAccountId, Name, Type FROM objMainAccount order by MainAccountId\r\n";
                script += "\t{\r\n";
                script += "\t\tresult += '<dt>';\r\n";
                script += "\t\tresult += '<GLCode><![CDATA[' + objMainAccount.MainAccountId + ']]></GLCode>';\r\n";
                script += "\t\tresult += '<Name><![CDATA[' + objMainAccount.Name + ']]></Name>';\r\n";
                script += "\t\tresult += '<Type><![CDATA[' + enum2str(objMainAccount.Type) + ']]></Type>';\r\n";
                script += "\t\tresult += '</dt>';\r\n";
                script += "\t}\r\n";
                script += "\tresult += '</ds>';\r\n";
                script += "\treturn result;\r\n";
                script += "}\r\n";

                // attempt compile and execute
                string result = client.runScript(context, script);
                try
                {
                    if (result.Substring(0, 5) == "Error")
                    {
                        MessageBox.Show(result, "ThisWorkbook_Startup");
                        return;
                    }
                }
                catch { }

                result = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>" + result;
                DataSet objDataSet = new DataSet();
                StringReader xmlSR = new StringReader(result);
                objDataSet.ReadXml(xmlSR);

                dataGridView1.DataSource = objDataSet.Tables[0];

                objDataSet = new DataSet();
                xmlSR = new StringReader(result);
                objDataSet.ReadXml(xmlSR);
                dataGridView4.DataSource = objDataSet.Tables[0];

                // CostCentres
                script = "";
                script += "str getCostCentres()\r\n";
                script += "{\r\n";
                script += "\tOMOperatingUnit objOMOperatingUnit;\r\n";
                script += "\tstr result = '';\r\n";
                script += "\t;\r\n";
                script += "\tresult += '<ds>';\r\n";
                script += "\twhile SELECT OMOperatingUnitNumber, Name FROM objOMOperatingUnit order by OMOperatingUnitNumber WHERE objOMOperatingUnit.OMOperatingUnitType == OMOperatingUnitType::OMCostCenter\r\n";
                script += "\t{\r\n";
                script += "\t\tresult += '<dt>';\r\n";
                script += "\t\tresult += '<UnitNumber><![CDATA[' + objOMOperatingUnit.OMOperatingUnitNumber + ']]></UnitNumber>';\r\n";
                script += "\t\tresult += '<Name><![CDATA[' + objOMOperatingUnit.Name + ']]></Name>';\r\n";
                script += "\t\tresult += '</dt>';\r\n";
                script += "\t}\r\n";
                script += "\tresult += '</ds>';\r\n";
                script += "\treturn result;\r\n";
                script += "}\r\n";

                // attempt compile and execute
                result = client.runScript(context, script);
                try
                {
                    if (result.Substring(0, 5) == "Error")
                    {
                        MessageBox.Show(result, "ThisWorkbook_Startup");
                        return;
                    }
                }
                catch { }

                result = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>" + result;
                objDataSet = new DataSet();
                xmlSR = new StringReader(result);
                objDataSet.ReadXml(xmlSR);

                dataGridView2.DataSource = objDataSet.Tables[0];

                // Departments
                script = "";
                script += "str getDepartments()\r\n";
                script += "{\r\n";
                script += "\tOMOperatingUnit objOMOperatingUnit;\r\n";
                script += "\tstr result = '';\r\n";
                script += "\t;\r\n";
                script += "\tresult += '<ds>';\r\n";
                script += "\twhile SELECT OMOperatingUnitNumber, Name FROM objOMOperatingUnit order by OMOperatingUnitNumber WHERE objOMOperatingUnit.OMOperatingUnitType == OMOperatingUnitType::OMDepartment\r\n";
                script += "\t{\r\n";
                script += "\t\tresult += '<dt>';\r\n";
                script += "\t\tresult += '<UnitNumber><![CDATA[' + objOMOperatingUnit.OMOperatingUnitNumber + ']]></UnitNumber>';\r\n";
                script += "\t\tresult += '<Name><![CDATA[' + objOMOperatingUnit.Name + ']]></Name>';\r\n";
                script += "\t\tresult += '</dt>';\r\n";
                script += "\t}\r\n";
                script += "\tresult += '</ds>';\r\n";
                script += "\treturn result;\r\n";
                script += "}\r\n";

                // attempt compile and execute
                result = client.runScript(context, script);
                try
                {
                    if (result.Substring(0, 5) == "Error")
                    {
                        MessageBox.Show(result, "ThisWorkbook_Startup");
                        return;
                    }
                }
                catch { }

                result = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>" + result;
                objDataSet = new DataSet();
                xmlSR = new StringReader(result);
                objDataSet.ReadXml(xmlSR);

                dataGridView3.DataSource = objDataSet.Tables[0];

                // Bank accounts
                script = "";
                script += "str getBankAccounts()\r\n";
                script += "{\r\n";
                script += "\tBankAccountTable objBankAccountTable;\r\n";
                script += "\tstr result = '';\r\n";
                script += "\t;\r\n";
                script += "\tresult += '<ds>';\r\n";
                script += "\twhile SELECT AccountID, Name FROM objBankAccountTable order by AccountID WHERE objBankAccountTable.ActiveTo <= DateTimeUtil::getSystemDateTime()\r\n";
                script += "\t{\r\n";
                script += "\t\tresult += '<dt>';\r\n";
                script += "\t\tresult += '<AccountID><![CDATA[' + objBankAccountTable.AccountID + ']]></AccountID>';\r\n";
                script += "\t\tresult += '<Name><![CDATA[' + objBankAccountTable.Name + ']]></Name>';\r\n";
                script += "\t\tresult += '</dt>';\r\n";
                script += "\t}\r\n";
                script += "\tresult += '</ds>';\r\n";
                script += "\treturn result;\r\n";
                script += "}\r\n";

                // attempt compile and execute
                result = client.runScript(context, script);
                try
                {
                    if (result.Substring(0, 5) == "Error")
                    {
                        MessageBox.Show(result, "ThisWorkbook_Startup");
                        return;
                    }
                }
                catch { }

                result = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>" + result;
                objDataSet = new DataSet();
                xmlSR = new StringReader(result);
                objDataSet.ReadXml(xmlSR);

                dataGridView5.DataSource = objDataSet.Tables[0];

                // Close connection
                client.Close();
            }
            catch { }
        }

        private void dataGridView1_DoubleClick(object sender, EventArgs e)
        {
            DataGridView dataGrid = (DataGridView)sender;
            DataGridViewCell cell = dataGrid.SelectedCells[0] as DataGridViewCell;
            string value = dataGrid.Rows[cell.RowIndex].Cells[0].Value.ToString();
            ExcelParent.ActiveCell.Value = value;
            this.Hide();
        }

        private void dataGridView2_DoubleClick(object sender, EventArgs e)
        {
            DataGridView dataGrid = (DataGridView)sender;
            DataGridViewCell cell = dataGrid.SelectedCells[0] as DataGridViewCell;
            string value = dataGrid.Rows[cell.RowIndex].Cells[0].Value.ToString();
            ExcelParent.ActiveCell.Value = value;
            this.Hide();
        }

        private void dataGridView3_DoubleClick(object sender, EventArgs e)
        {
            DataGridView dataGrid = (DataGridView)sender;
            DataGridViewCell cell = dataGrid.SelectedCells[0] as DataGridViewCell;
            string value = dataGrid.Rows[cell.RowIndex].Cells[0].Value.ToString();
            ExcelParent.ActiveCell.Value = value;
            this.Hide();
        }

        private void dataGridView4_DoubleClick(object sender, EventArgs e)
        {
            DataGridView dataGrid = (DataGridView)sender;
            DataGridViewCell cell = dataGrid.SelectedCells[0] as DataGridViewCell;
            string value = dataGrid.Rows[cell.RowIndex].Cells[0].Value.ToString();
            ExcelParent.ActiveCell.Value = value;
            this.Hide();
        }

        private void dataGridView5_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            DataGridView dataGrid = (DataGridView)sender;
            DataGridViewCell cell = dataGrid.SelectedCells[0] as DataGridViewCell;
            string value = dataGrid.Rows[cell.RowIndex].Cells[0].Value.ToString();
            ExcelParent.ActiveCell.Value = value;
            this.Hide();
        }
    }
}
